package com.union.core.sys.dao.impl;

import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;
import org.apache.log4j.Logger;
import com.hnjowe.webdb.DBOperation;
import com.union.core.sys.dao.PublicSentimentModuleDao;
import com.union.core.sys.model.PublicSentimentModule;
import com.union.core.sys.service.SysService;
import com.union.core.sys.util.BaseDateConfig;

/**
 * 舆情管理DAO实现类 filename: SysManagerDaoImpl.java project: CCTR
 * 
 * @company Hainan Jowe Technology Ltd
 * @author wull
 * @create in 2012-10-31
 * @version 1.0
 */
public class PublicSentimentModuleDaoImpl implements PublicSentimentModuleDao {

	private static Logger log = Logger.getLogger(SysManagerDaoImpl.class
			.getName());

	private DBOperation opera;
	private PublicSentimentModule publicSentimentModule;
	private SysService sysService = SysService.getInstance();

	public PublicSentimentModuleDaoImpl() {
	}

	public boolean addModule(PublicSentimentModule publicSentimentModule) {
		boolean result = false;
		try {
			opera = new DBOperation();
			opera.transcationBegin();
			StringBuffer sqlBuf = new StringBuffer();
			sqlBuf.append("insert into public_sentiment");
			sqlBuf.append("(pubsentm_name,pubsentm_type,pubsentm_details,ORGANIZATION_ID,");
			sqlBuf.append(" pubsentm_file,status,create_time,subject_time,staff_id,update_staff,update_time,writer_name,staff_name,organization_name,file_path,receive_list) ");
			sqlBuf.append("values(?,?,?,?,?,?,SYSDATE(),?,?,?,SYSDATE(),?,?,?,?,?);");
			opera.prepStatment(sqlBuf.toString());
			opera.setString(1, publicSentimentModule.getPUBSENTM_NAME());
			opera.setInt(2, publicSentimentModule.getPUBSENTM_TYPE());
			opera.setString(3, publicSentimentModule.getPUBSENTM_DETAILS());
			opera.setInt(4, publicSentimentModule.getORGANIZATION_ID());
			opera.setString(5, publicSentimentModule.getPUBSENTM_FILE());
			opera.setInt(6, publicSentimentModule.getSTATUS());
			opera.setString(7, publicSentimentModule.getSUBJECT_TIME());
			opera.setInt(8, sysService.getSessionListener().getMANAGER_ID());
			opera.setInt(9, sysService.getSessionListener().getMANAGER_ID());
			opera.setString(10, publicSentimentModule.getWRITER_NAME());
			opera.setString(11, publicSentimentModule.getStaff_name());
			opera.setString(12, publicSentimentModule.getOrganization_name());
			opera.setString(13, publicSentimentModule.getFile_path());
			opera.setString(14, publicSentimentModule.getReceive_list());
			 opera.execute();
			result = opera.transcationCommit();
			if(result == false)
			{
				log.error("sql="+opera.getPres().toString());
				return false;
			}
			
			
			
		} catch (Exception e) {
			log.error(e);
			log.error("sql="+opera.getPres().toString());
		} finally {
			opera.closePres();
			opera.closeConn();
		}
		return result;      
	}
	
	public boolean deleteModule(int pubSentId) {
		boolean result = false;
		try {
			opera = new DBOperation();
			opera.transcationBegin();
			StringBuffer sqlBuf = new StringBuffer();
			sqlBuf.append("delete from public_sentiment");
			sqlBuf.append(" where pubsentm_id = ? ");
			opera.prepStatment(sqlBuf.toString());
			
			opera.setInt(1, pubSentId);

			
			opera.execute();
			
			result = opera.transcationCommit();
			if(result == false)
			{
				log.error("sql="+opera.getPres().toString());
				return false;
			}
			StringBuffer sqlBuf1 = new StringBuffer();
			sqlBuf1.append("delete from public_sentiment_audi");
			sqlBuf1.append(" where pubsentm_id=?");
			opera.prepStatment(sqlBuf1.toString());
			opera.setInt(1, pubSentId);

			
			opera.execute();
			
			result = opera.transcationCommit();
			if (result == false) {
				log.error("sql="+opera.getPres().toString());
				return false;
			}
			
			
		} catch (Exception e) {
			log.error(e);
			log.error("sql="+opera.getPres().toString());
		} finally {
			opera.closePres();
			opera.closeConn();
		}
		return result;      
	}
	
	
	public boolean beianModule(int pubSentId) {
		boolean result = false;
		try {
			opera = new DBOperation();
			opera.transcationBegin();
			StringBuffer sqlBuf = new StringBuffer();
			sqlBuf.append("update public_sentiment");
			sqlBuf.append(" set deal_status = '1', ");
			sqlBuf.append(" update_staff = ?, ");
			sqlBuf.append(" update_time = SYSDATE() ");
			sqlBuf.append(" where pubsentm_id = ? ");
			opera.prepStatment(sqlBuf.toString());
			opera.setInt(1, sysService.getSessionListener().getPERSONNEL_ID());
			opera.setInt(2, pubSentId);

			boolean flag = true;
			flag = opera.execute();
			if (flag == false) {
				log.error("sql="+opera.getPres().toString());
				return flag;
			}
			result = opera.transcationCommit();
			if(result == false)
			{
				log.error("sql="+opera.getPres().toString());
				return false;
			}
			StringBuffer sqlBuf1 = new StringBuffer();
			sqlBuf1.append("insert into public_sentiment_log");
			sqlBuf1.append("(pubsentm_id,update_staff,update_time,deal_status,oper_status) ");
			sqlBuf1.append("values(?,?,SYSDATE(),'1',?);");
			opera.prepStatment(sqlBuf1.toString());
			opera.setInt(1, pubSentId);
			opera.setInt(2, sysService.getSessionListener().getPERSONNEL_ID());
			opera.setInt(3, BaseDateConfig.UPDATE_DATA);
			flag = true;
			flag = opera.execute();
			if (flag == false) {
				log.error("sql="+opera.getPres().toString());
				return flag;
			}
			result = opera.transcationCommit();
			if (result == false) {
				log.error("sql="+opera.getPres().toString());
				return flag;
			}
		} catch (Exception e) {
			log.error(e);
			log.error("sql="+opera.getPres().toString());
		} finally {
			opera.closePres();
			opera.closeConn();
		}
		return result;      
	}
	
	
	
	
	public int getPubSentListCount(String startDate,String endDate,int status,int dealType,int orgId,int staffId,int queryType,String queryData)
	{
		int totalSize = 0;// 某条件查询下记录的总条数
		StringBuffer sql_first = new StringBuffer(); // 条件1
		StringBuffer sql_second =  new StringBuffer(); // 条件2
		StringBuffer sql_third =	new StringBuffer();
		if (null!=startDate && !"null".equals(startDate) &&!"".equals(startDate) ) {
				sql_first.append(" and create_time >  DATE_FORMAT('");
				sql_first.append(startDate);
				sql_first.append("','%Y~%m~%d %k.%i.%s' )");
		}
		if (null!=endDate && !"null".equals(endDate) &&!"".equals(endDate) ) {
			sql_second.append(" and create_time <  DATE_FORMAT('");
			sql_second.append(endDate);
			sql_second.append("','%Y~%m~%d %k.%i.%s' )");
		}
		if(queryType!=0){
			switch(queryType)
			{
				case 1:
					if (null!=queryData && !"null".equals(queryData) &&!"".equals(queryData) ) 
					{
						sql_second.append(" and pubsentm_name like '%");
						sql_second.append(queryData.trim());
						sql_second.append("%'");
					}
					break;
				case 2:
					if (null!=queryData && !"null".equals(queryData) &&!"".equals(queryData) ) 
					{
						sql_second.append(" and pubsentm_id  =  ");
						sql_second.append(queryData);
					}
					break;
				case 3:
					if (null!=queryData && !"null".equals(queryData) &&!"".equals(queryData) ) 
					{
						sql_second.append(" and organization_name LIKE '%");
						sql_second.append(queryData.trim());
						sql_second.append("%'");
					}
					break;
			}
		}
		try{
			opera = new DBOperation();
			opera.transcationBegin();
			StringBuffer sql = new  StringBuffer();
			sql.append("select count(*) as totalSize from public_sentiment t");
			sql.append(" where status = ? ");
			if(dealType!=-1){
				sql.append(" and deal_status = ? ");
			}
			if(orgId!=0){
				sql.append(" and receive_list like '%,");
				sql.append(orgId);
				sql.append(",%'");
			}
			if(staffId!=0){
				sql.append(" and staff_id =");
				sql.append(staffId);
			}
			sql.append(sql_first);//条件1
			sql.append(sql_second);//条件2
			sql.append(sql_third);// 条件3
			opera.prepStatment(sql.toString());
			
			opera.setInt(1, status);
			if(dealType!=-1){
				opera.setInt(2, dealType);
			}
			log.error("sql="+opera.getPres().toString());
			ResultSet res = opera.executeQuery();
			
			log.error("sql="+opera.getPres().toString());
			if (res.next()) {
				totalSize = res.getInt(1);
			}
		} catch (Exception e) {
			log.error(e);
		} finally {
			opera.closePres();
			opera.closeConn();
		}
		return totalSize;
	}
	
	public List<PublicSentimentModule> getPubSentList(String startDate,String endDate,int status,int dealType,int orgId,int staffId,int queryType,String queryData,int start, int PageSize)
	{
		int totalSize = 0;// 某条件查询下记录的总条数
		StringBuffer sql_first = new StringBuffer(); // 条件1
		StringBuffer sql_second =  new StringBuffer(); // 条件2
		StringBuffer sql_third = new StringBuffer();
		StringBuffer orderbySql = new StringBuffer();
		List<PublicSentimentModule> pubSentList = new ArrayList<PublicSentimentModule>();
		if (null!=startDate && !"null".equals(startDate) &&!"".equals(startDate) ) {
				sql_first.append(" and create_time >  DATE_FORMAT('");
				sql_first.append(startDate);
				sql_first.append("','%Y~%m~%d %k.%i.%s' )");
		}
		if (null!=endDate && !"null".equals(endDate) &&!"".equals(endDate) ) {
			sql_second.append(" and create_time <  DATE_FORMAT('");
			sql_second.append(endDate);
			sql_second.append("','%Y~%m~%d %k.%i.%s' )");
		}
	
		
		if(queryType!=0){
			switch(queryType)
			{
				case 1:
					if (null!=queryData && !"null".equals(queryData) &&!"".equals(queryData) ) 
					{
						sql_second.append(" and pubsentm_name  like '%");
						sql_second.append(queryData.trim());
						sql_second.append("%'");
					}
					break;
				case 2:
					if (null!=queryData && !"null".equals(queryData) &&!"".equals(queryData) ) 
					{
						sql_second.append(" and pubsentm_id  =  ");
						sql_second.append(queryData);
					}
					break;
				case 3:
					if (null!=queryData && !"null".equals(queryData) &&!"".equals(queryData) ) 
					{
						sql_second.append(" and organization_name like '%");
						sql_second.append(queryData.trim());
						sql_second.append("%'");
					}
					break;
			}
		}
		orderbySql.append(" order by pubsentm_id desc "); 
		
		try{
			opera = new DBOperation();
			opera.transcationBegin();
			StringBuffer sql = new  StringBuffer();
			PublicSentimentModule publicSentimentModule;
				
			sql.append("select pubsentm_id,organization_id,pubsentm_name,pubsentm_type,subject_time,organization_name,staff_name,file_path,receive_list,CREATE_TIME from public_sentiment t ");
			sql.append(" where status = ? ");
			if(dealType!=-1){
				sql.append(" and deal_status = ? ");
			}
			if(orgId!=0){
				sql.append(" and receive_list like '%,");
				sql.append(orgId);
				sql.append(",%'");
			}
			if(staffId!=0){
				sql.append(" and staff_id =");
				sql.append(staffId);
			}
			sql.append(sql_first);//条件1
			sql.append(sql_second);//条件2
			sql.append(sql_third);// 条件3
			sql.append(orderbySql);// 条件3
			sql.append(" limit ?,? ");// 条件3
			opera.prepStatment(sql.toString());
			opera.setInt(1, status);
			if(dealType!=-1){
				opera.setInt(2, dealType);
				opera.setInt(3, start); 
				opera.setInt(4, PageSize);
			}else{
				opera.setInt(2, start); 
				opera.setInt(3, PageSize);
			}
			log.info("sql="+opera.getPres().toString());
			ResultSet res = opera.executeQuery();
			log.info("sql="+opera.getPres().toString());
			int i =0; 
			while (res.next()) {
				publicSentimentModule = new PublicSentimentModule();
				publicSentimentModule.setPUBSENTM_ID(res.getInt(1));
				publicSentimentModule.setORGANIZATION_ID(res.getInt(2));
				publicSentimentModule.setPUBSENTM_NAME(res.getString(3));
				publicSentimentModule.setPUBSENTM_TYPE(res.getInt(4));
				publicSentimentModule.setSUBJECT_TIME(res.getString(5));
				publicSentimentModule.setOrganization_name(res.getString(6));
				publicSentimentModule.setStaff_name(res.getString(7));
				publicSentimentModule.setFile_path(res.getString(8));
				publicSentimentModule.setReceive_list(res.getString(9));
				publicSentimentModule.setCREATE_TIME(res.getString(10));
				pubSentList.add(i,publicSentimentModule);
				i++; 
			}
			
		} catch (Exception e) {
			log.error(e);
		} finally {
			opera.closePres();
			opera.closeConn();
		}
		return pubSentList;
		
	}

	public PublicSentimentModule getPublicSentimentModuleById(int publicSentId)
	{
		PublicSentimentModule publicSentimentModule = new PublicSentimentModule();
		DBOperation opera = new DBOperation();
		try {
			opera.transcationBegin();
			StringBuffer sqlBuf = new StringBuffer(); 
			sqlBuf.append("select PUBSENTM_ID,PUBSENTM_NAME,PUBSENTM_TYPE,PUBSENTM_DETAILS,PUBSENTM_FILE,");
			sqlBuf.append("STATUS,CREATE_TIME,SUBJECT_TIME,WRITER_NAME,STAFF_ID,ORGANIZATION_ID,UPDATE_STAFF,UPDATE_TIME,organization_name,staff_name,file_path,receive_list");
			sqlBuf.append(" from public_sentiment ");
			sqlBuf.append(" where pubsentm_id = ? ");

			
			opera.prepStatment( sqlBuf.toString());
			opera.setInt(1, publicSentId);
			log.info(opera.getPres().toString());
			ResultSet res = opera.executeQuery();
			if (res.next()) {
				publicSentimentModule.setPUBSENTM_ID(res.getInt(1));
				publicSentimentModule.setPUBSENTM_NAME(res.getString(2));
				publicSentimentModule.setPUBSENTM_TYPE(res.getInt(3));
				publicSentimentModule.setPUBSENTM_DETAILS(res.getString(4));
				publicSentimentModule.setPUBSENTM_FILE(res.getString(5));
				publicSentimentModule.setSTATUS(res.getInt(6));
				publicSentimentModule.setCREATE_TIME(res.getString(7));
				publicSentimentModule.setSUBJECT_TIME(res.getString(8));
				publicSentimentModule.setWRITER_NAME(res.getString(9));
				publicSentimentModule.setSTAFF_ID(res.getInt(10));
				publicSentimentModule.setORGANIZATION_ID(res.getInt(11));
				publicSentimentModule.setUPDATE_STAFF(res.getInt(12));
				publicSentimentModule.setUPDATE_TIME(res.getString(13));
				publicSentimentModule.setOrganization_name(res.getString(14));
				publicSentimentModule.setStaff_name(res.getString(15));
				publicSentimentModule.setFile_path(res.getString(16));
				publicSentimentModule.setReceive_list(res.getString(17));
			}
		} catch (Exception e) {
			log.error(e);
			log.error(opera.getPres().toString());
		} finally {
			opera.closePres();
			opera.closeConn();
		}
		return publicSentimentModule;
		
		
	}


	public int getModuleId(String actionFile) {
		// TODO Auto-generated method stub
		return 0;
	}

	public List<PublicSentimentModule> getModuleList(int moduleTypeId) {
		// TODO Auto-generated method stub
		return null;
	}

	public List<PublicSentimentModule> getModuleList(int state, int moduleTypeId) {
		// TODO Auto-generated method stub
		return null;
	}

	public boolean modifyModule(PublicSentimentModule PublicSentimentModule) {
		// TODO Auto-generated method stub
		return false;
	}

	public boolean modifyModule(int moduleId, int state) {
		// TODO Auto-generated method stub
		return false;
	}

	public void setPublicSentimentModule(
			PublicSentimentModule publicSentimentModule) {
		this.publicSentimentModule = publicSentimentModule;
	}

	public PublicSentimentModule getPublicSentimentModule() {
		return publicSentimentModule;
	}

	
}
